﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace UDS.Components
{
    /// <summary>
    /// CM 的摘要说明。
    /// </summary>
    public class CM
    {
        #region 添加客户

        /// <summary>
        /// 添加客户
        /// </summary>
        /// <param name="client"></param>
        /// <returns>返回客户id</returns>
        public int AddClinet(ClientInfo client)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@birthday", SqlDbType.DateTime, 8, client.Birthday),
                Database.MakeInParam("@chieflinkmanid", SqlDbType.Int, 4, client.ChiefLinkmanID),
                Database.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, DateTime.Now),
                Database.MakeInParam("@AddmanID", SqlDbType.Int, 4, client.AddManID),
                Database.MakeInParam("@ShortName", SqlDbType.NVarChar, 100, client.ClientShortName),
                Database.MakeInParam("@Name", SqlDbType.NVarChar, 100, client.ClientName),
                Database.MakeInParam("@ContactTimes", SqlDbType.Int, 4, client.ContactTimes),
                Database.MakeInParam("@SellPhase", SqlDbType.NVarChar, 100, client.SellPhase),
                Database.MakeInParam("@BargainPrognosis", SqlDbType.NVarChar, 100, client.BargainPrognosis),
                Database.MakeInParam("@Fee", SqlDbType.Int, 4, client.Fee),
                Database.MakeInParam("@Linkman_Telephone", SqlDbType.NVarChar, 100, client.Linkman_Telephone),
                Database.MakeInParam("@CurStatus", SqlDbType.NVarChar, 100, client.CurStatus),
                Database.MakeInParam("@Type", SqlDbType.NVarChar, 100, client.Type),
                Database.MakeInParam("@Affiliatedarea", SqlDbType.NVarChar, 100, client.Affiliatedarea),
                Database.MakeInParam("@URL", SqlDbType.NVarChar, 100, client.URL),
                Database.MakeInParam("@ZIP", SqlDbType.NVarChar, 100, client.ZIP),
                Database.MakeInParam("@Address", SqlDbType.NVarChar, 200, client.Address),
                Database.MakeInParam("@CompanyProperty", SqlDbType.NVarChar, 100, client.EnterpriseType),
                Database.MakeInParam("@Calling", SqlDbType.NVarChar, 2000, client.Calling),
                Database.MakeInParam("@CompanySize", SqlDbType.NVarChar, 100, client.CompanySize),
                Database.MakeInParam("@Money", SqlDbType.NVarChar, 100, client.Money),
                Database.MakeInParam("@Operation", SqlDbType.NVarChar, 100, client.Operation),
                Database.MakeInParam("@Introduce", SqlDbType.NVarChar, 1000, client.Introduce),
                Database.MakeInParam("@ITGrade", SqlDbType.NVarChar, 1000, client.ITGrade),
                Database.MakeInParam("@PCNumber", SqlDbType.Int, 4, client.PCNumber),
                Database.MakeInParam("@Net", SqlDbType.NVarChar, 100, client.Net),
                Database.MakeInParam("@ITStaffs", SqlDbType.Int, 4, client.ITStaffs),
                Database.MakeInParam("@ITDepartment", SqlDbType.NVarChar, 100, client.ITDepartment),
                Database.MakeInParam("@Principal", SqlDbType.NVarChar, 100, client.Principal),
                Database.MakeInParam("@System", SqlDbType.NVarChar, 100, client.System),
                Database.MakeInParam("@Customer", SqlDbType.NVarChar, 100, client.ClientSource),
                Database.MakeInParam("@ClientInitiative", SqlDbType.NVarChar, 100, client.ClientInitiative)
            };
            return (data.RunProc("dbo.sproc_CM_AddClient", parameters));
        }

        #endregion

        #region 修改客户

        /// <summary>
        /// 修改客户
        /// </summary>
        /// <param name="client"></param>
        public void UpdateClient(ClientInfo client)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ID", SqlDbType.Int, 4, client.ID),
                Database.MakeInParam("@birthday", SqlDbType.DateTime, 8, client.Birthday),
                Database.MakeInParam("@chieflinkmanid", SqlDbType.Int, 4, client.ChiefLinkmanID),
                Database.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, DateTime.Now),
                Database.MakeInParam("@AddmanID", SqlDbType.Int, 4, client.AddManID),
                Database.MakeInParam("@ShortName", SqlDbType.NVarChar, 100, client.ClientShortName),
                Database.MakeInParam("@Name", SqlDbType.NVarChar, 100, client.ClientName),
                Database.MakeInParam("@ContactTimes", SqlDbType.Int, 4, client.ContactTimes),
                Database.MakeInParam("@SellPhase", SqlDbType.NVarChar, 100, client.SellPhase),
                Database.MakeInParam("@BargainPrognosis", SqlDbType.NVarChar, 100, client.BargainPrognosis),
                Database.MakeInParam("@Fee", SqlDbType.Int, 4, client.Fee),
                Database.MakeInParam("@Linkman_Telephone", SqlDbType.NVarChar, 100, client.Linkman_Telephone),
                Database.MakeInParam("@CurStatus", SqlDbType.NVarChar, 100, client.CurStatus),
                Database.MakeInParam("@Type", SqlDbType.NVarChar, 100, client.Type),
                Database.MakeInParam("@Affiliatedarea", SqlDbType.NVarChar, 100, client.Affiliatedarea),
                Database.MakeInParam("@URL", SqlDbType.NVarChar, 100, client.URL),
                Database.MakeInParam("@ZIP", SqlDbType.NVarChar, 100, client.ZIP),
                Database.MakeInParam("@Address", SqlDbType.NVarChar, 200, client.Address),
                Database.MakeInParam("@CompanyProperty", SqlDbType.NVarChar, 100, client.EnterpriseType),
                Database.MakeInParam("@Calling", SqlDbType.NVarChar, 2000, client.Calling),
                Database.MakeInParam("@CompanySize", SqlDbType.NVarChar, 100, client.CompanySize),
                Database.MakeInParam("@Money", SqlDbType.NVarChar, 100, client.Money),
                Database.MakeInParam("@Operation", SqlDbType.NVarChar, 100, client.Operation),
                Database.MakeInParam("@Introduce", SqlDbType.NVarChar, 1000, client.Introduce),
                Database.MakeInParam("@ITGrade", SqlDbType.NVarChar, 1000, client.ITGrade),
                Database.MakeInParam("@PCNumber", SqlDbType.Int, 4, client.PCNumber),
                Database.MakeInParam("@Net", SqlDbType.NVarChar, 100, client.Net),
                Database.MakeInParam("@ITStaffs", SqlDbType.Int, 4, client.ITStaffs),
                Database.MakeInParam("@ITDepartment", SqlDbType.NVarChar, 100, client.ITDepartment),
                Database.MakeInParam("@Principal", SqlDbType.NVarChar, 100, client.Principal),
                Database.MakeInParam("@System", SqlDbType.NVarChar, 100, client.System),
                Database.MakeInParam("@Customer", SqlDbType.NVarChar, 100, client.ClientSource),
                Database.MakeInParam("@ClientInitiative", SqlDbType.NVarChar, 100, client.ClientInitiative),
                Database.MakeInParam("@FirstContactTime", SqlDbType.DateTime, 8, client.FirstContactTime),
                Database.MakeInParam("@NextContactTime", SqlDbType.DateTime, 8, client.NextContactTime),
                Database.MakeInParam("@ContactTime", SqlDbType.DateTime, 8, client.ContactTime)
            };
            data.RunProc("dbo.sproc_CM_UpdateClient", parameters);
        }

        #endregion

        #region 删除客户

        /// <summary>
        /// 删除客户
        /// </summary>
        /// <param name="id">客户id</param>
        public void DelClient(int id)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, id)
            };
            data.RunProc("dbo.sproc_CM_DelClient", parameters);
        }

        #endregion

        #region 得到所有客户

        public SqlDataReader GetAllClient()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_GetAllClient");
        }

        #endregion

        #region 根据客户id得到联络人

        public SqlDataReader GetLinkmanFromClient(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };

            return data.RunDataReader("dbo.sproc_CM_GetAllLinkmanFromClient", parameters);
        }

        #endregion

        #region 根据客户名称得到客户id

        public SqlDataReader GetClientIDByName(string clientname)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@name", SqlDbType.NVarChar, 100, clientname)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientIDByName", parameters);
        }

        #endregion

        #region 根据客户name得到客户信息

        public SqlDataReader GetClientInfoByName(string clientname)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientname", SqlDbType.NVarChar, 100, clientname)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientInfoByName", parameters);
        }

        #endregion

        #region 根据客户id得到客户信息(客户id=0得到全部客户)

        public SqlDataReader GetClientInfo(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientInfo", parameters);
        }

        public ClientInfo GetClientAllInfo(int clientid)
        {
            var client = new ClientInfo();
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetClientAllInfo", parameters))
            {
                while (dr.Read())
                {
                    client.ID = Int32.Parse(dr["id"].ToString());
                    client.ClientName = dr["Name"].ToString();
                    client.ClientShortName = dr["ShortName"].ToString();
                    client.ChiefLinkmanID = Int32.Parse(dr["ChiefLinkmanID"].ToString());
                    client.Birthday = DateTime.Parse(dr["Birthday"].ToString());
                    client.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
                    client.ContactTimes = Int32.Parse(dr["ContactTimes"].ToString());
                    client.SellPhase = dr["SellPhase"].ToString();
                    client.BargainPrognosis = dr["BargainPrognosis"].ToString();
                    client.Fee = Int32.Parse(dr["Fee"].ToString());
                    client.AddManID = Int32.Parse(dr["AddManID"].ToString());
                    client.Linkman_Telephone = dr["Linkman_Telephone"].ToString();
                    client.CurStatus = dr["CurStatus"].ToString();
                    client.Address = dr["address"].ToString();
                    client.Affiliatedarea = dr["affiliatedarea"].ToString();
                    client.Calling = dr["calling"].ToString();
                    client.ClientInitiative = dr["ClientInitiative"].ToString();
                    client.ClientSource = dr["customer"].ToString();
                    client.ClientTrade = dr["calling"].ToString();
                    client.ClientType = dr["type"].ToString();
                    client.CompanyProperty = dr["companyproperty"].ToString();
                    client.CompanySize = dr["companysize"].ToString();
                    client.CurStatus = dr["curstatus"].ToString();
                    client.Customer = dr["customer"].ToString();
                    client.EnterpriseType = dr["CompanyProperty"].ToString();
                    client.Introduce = dr["introduce"].ToString();
                    client.ITDepartment = dr["itdepartment"].ToString();
                    client.ITGrade = dr["itgrade"].ToString();
                    client.ITStaffs = Int32.Parse(dr["itstaffs"].ToString());
                    client.Linkman_Telephone = dr["Linkman_Telephone"].ToString();
                    client.Money = dr["money"].ToString();
                    client.Net = dr["Net"].ToString();
                    client.Operation = dr["operation"].ToString();
                    client.PCNumber = Int32.Parse(dr["pcnumber"].ToString());
                    client.Principal = dr["principal"].ToString();
                    client.SellPhase = dr["sellphase"].ToString();
                    client.System = dr["system"].ToString();
                    client.Type = dr["type"].ToString();
                    client.URL = dr["URL"].ToString();
                    client.ZIP = dr["ZIP"].ToString();

                    if (dr["ContactTime"] != DBNull.Value)
                    {
                        client.ContactTime = DateTime.Parse(dr["ContactTime"].ToString());
                    }
                    else
                    {
                        client.ContactTime = DateTime.Parse("1900-1-1");
                    }

                    if (dr["firstcontacttime"] != DBNull.Value)
                    {
                        client.FirstContactTime = DateTime.Parse(dr["firstcontacttime"].ToString());
                    }
                    else
                    {
                        client.FirstContactTime = DateTime.Parse("1900-1-1");
                    }

                    if (dr["nextcontacttime"] != DBNull.Value)
                    {
                        client.NextContactTime = DateTime.Parse(dr["nextcontacttime"].ToString());
                    }
                    else
                    {
                        client.NextContactTime = DateTime.Parse("1900-1-1");
                    }
                }
            }
            return (client);
        }

        #endregion

        #region 根据协同人id得到客户信息

        public SqlDataReader GetClientInfoBycooperatorID(int cooperatorID)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@cooperatorID", SqlDbType.Int, 4, cooperatorID)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientInfoByCooperatorID", parameters);
        }

        #endregion

        #region 添加联系人

        public int AddLinkman(Linkman linkman)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ClientID", SqlDbType.Int, 4, linkman.ClientID),
                Database.MakeInParam("@name", SqlDbType.NVarChar, 50, linkman.Name),
                Database.MakeInParam("@mobile", SqlDbType.NVarChar, 50, linkman.Mobile),
                Database.MakeInParam("@telephone", SqlDbType.NVarChar, 50, linkman.Telephone),
                Database.MakeInParam("@position", SqlDbType.NVarChar, 50, linkman.Position),
                Database.MakeInParam("@email", SqlDbType.NVarChar, 50, linkman.Email),
                Database.MakeInParam("@gender", SqlDbType.Bit, 1, linkman.Gender),
                Database.MakeInParam("@description", SqlDbType.NVarChar, 50, linkman.Description),
                Database.MakeInParam("@address", SqlDbType.NVarChar, 100, linkman.Address),
                Database.MakeInParam("@family", SqlDbType.NVarChar, 200, linkman.Family),
            };
            return (data.RunProc("dbo.sproc_CM_AddLinkman", parameters));
        }

        #endregion

        #region 修改联系人

        public void UpdateLinkman(Linkman linkman)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ID", SqlDbType.Int, 4, linkman.ID),
                Database.MakeInParam("@ClientID", SqlDbType.Int, 4, linkman.ClientID),
                Database.MakeInParam("@name", SqlDbType.NVarChar, 50, linkman.Name),
                Database.MakeInParam("@mobile", SqlDbType.NVarChar, 50, linkman.Mobile),
                Database.MakeInParam("@telephone", SqlDbType.NVarChar, 50, linkman.Telephone),
                Database.MakeInParam("@position", SqlDbType.NVarChar, 50, linkman.Position),
                Database.MakeInParam("@email", SqlDbType.NVarChar, 50, linkman.Email),
                Database.MakeInParam("@gender", SqlDbType.Bit, 1, linkman.Gender),
                Database.MakeInParam("@description", SqlDbType.NVarChar, 50, linkman.Description),
                Database.MakeInParam("@address", SqlDbType.NVarChar, 100, linkman.Address),
                Database.MakeInParam("@family", SqlDbType.NVarChar, 200, linkman.Family),
            };
            data.RunProc("dbo.sproc_CM_UpdateLinkman", parameters);
        }

        #endregion

        #region 删除联系人

        public bool DelLinkman(Linkman linkman)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@LinkmanID", SqlDbType.Int, 4, linkman.ID),
            };
            return (data.RunProc("dbo.sproc_CM_DelLinkman", parameters) == 0 ? true : false);
        }

        #endregion

        #region 得到所有联系人

        public SqlDataReader GetAllLinkman()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_GetAllLinkman");
        }

        public SqlDataReader GetAllContactLinkman()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_GetAllContactLinkman");
        }

        #endregion

        #region 根据id得到联系人

        public SqlDataReader GetLinkmanByID(string linkmanid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@LinkmanID", SqlDbType.NVarChar, 100, linkmanid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetLinkmanByID", parameters);
        }

        public Linkman GetLinkmanStructByID(string linkmanid)
        {
            var data = new Database();
            var linkman = new Linkman();
            var parameters = new[]
            {
                Database.MakeInParam("@LinkmanID", SqlDbType.NVarChar, 100, linkmanid)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetLinkmanByID", parameters))
            {
                while (dr.Read())
                {
                    linkman.Address = dr["address"].ToString();
                    linkman.ClientID = Int32.Parse(dr["clientid"].ToString());
                    linkman.Description = dr["description"].ToString();
                    linkman.Email = dr["email"].ToString();
                    linkman.Family = dr["family"].ToString();
                    linkman.Gender = Convert.ToBoolean(dr["gender"]);
                    linkman.ID = Int32.Parse(dr["id"].ToString());
                    linkman.Mobile = dr["mobile"].ToString();
                    linkman.Name = dr["name"].ToString();
                    linkman.Position = dr["position"].ToString();
                    linkman.Telephone = dr["telephone"].ToString();
                }
            }

            return (linkman);
        }

        #endregion

        #region 得到除了给定ids的其他联系人

        public SqlDataReader GetRemainLinkmen(string ids)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ids", SqlDbType.NVarChar, 300, ids)
            };

            try
            {
                // run the stored procedure
                return data.RunDataReader("dbo.sproc_CM_GetRemainLinkmen", parameters);
            }
            catch (Exception ex)
            {
                Logger.Log(ex.ToString());
                return null;
            }
        }

        #endregion

        #region 根据客户添加人得到与其有关的联系人

        public SqlDataReader GetLinkmenByClientAddmanID(int id)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@id", SqlDbType.Int, 4, id)
            };

            try
            {
                // run the stored procedure
                return data.RunDataReader("dbo.sproc_CM_GetLinkmenByClientAddmanID", parameters);
            }
            catch (Exception ex)
            {
                Logger.Log(ex.ToString());
                return null;
            }
        }

        #endregion

        #region 添加接触情况

        public int AddContact(Contact contact)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ClientID", SqlDbType.Int, 4, contact.ClientID),
                Database.MakeInParam("@MarketmanID", SqlDbType.Int, 4, contact.StaffID),
                Database.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, contact.UpdateTime),
                Database.MakeInParam("@ContactTime", SqlDbType.DateTime, 8, contact.ContactTime),
                Database.MakeInParam("@ContactAim", SqlDbType.NVarChar, 500, contact.ContactAim),
                Database.MakeInParam("@SellMoney", SqlDbType.Int, 4, contact.SellMoney),
                Database.MakeInParam("@ContactTimes", SqlDbType.Int, 4, contact.ContactTimes),
                Database.MakeInParam("@BargainPrognosis", SqlDbType.NVarChar, 50, contact.BargainPrognosis),
                Database.MakeInParam("@ContactType", SqlDbType.NVarChar, 50, contact.ContactType),
                Database.MakeInParam("@CurStatus", SqlDbType.NVarChar, 100, contact.ContactStatus),
                Database.MakeInParam("@Fee", SqlDbType.Int, 4, contact.ThisFee),
                Database.MakeInParam("@FeeUsed", SqlDbType.NVarChar, 100, contact.FeeUsed),
                Database.MakeInParam("@ContactContent", SqlDbType.NVarChar, 1000, contact.ContactContent),
                Database.MakeInParam("@NextContactAim", SqlDbType.NVarChar, 1000, contact.NextContactAim),
                Database.MakeInParam("@NextContactTime", SqlDbType.DateTime, 8, contact.NextContactTime),
            };
            contact.ID = data.RunProc("dbo.sproc_CM_AddContact", parameters);


            foreach (Cooperater cooperater in contact.arrCooperater)
            {
                var prams1 = new[]
                {
                    Database.MakeInParam("@staffid", SqlDbType.Int, 4, cooperater.StaffID),
                    Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
                };
                data.RunProc("dbo.sproc_CM_AddContact_Cooperater", prams1);
            }
            foreach (Linkman linkman in contact.arrLinkman)
            {
                var prams2 = new[]
                {
                    Database.MakeInParam("@staffid", SqlDbType.Int, 4, linkman.ID),
                    Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
                };
                data.RunProc("dbo.sproc_CM_AddContact_Linkman", prams2);
            }
            return (contact.ID);
        }

        #endregion

        #region 修改接触情况

        public void UpdateContact(Contact contact)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ID", SqlDbType.Int, 4, contact.ID),
                Database.MakeInParam("@ClientID", SqlDbType.Int, 4, contact.ClientID),
                Database.MakeInParam("@MarketmanID", SqlDbType.Int, 4, contact.StaffID),
                Database.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, contact.ContactTime),
                Database.MakeInParam("@ContactTime", SqlDbType.DateTime, 8, contact.ContactTime),
                Database.MakeInParam("@ContactAim", SqlDbType.NVarChar, 500, contact.ContactAim),
                Database.MakeInParam("@SellMoney", SqlDbType.Int, 4, contact.SellMoney),
                Database.MakeInParam("@BargainPrognosis", SqlDbType.NVarChar, 50, contact.BargainPrognosis),
                Database.MakeInParam("@ContactType", SqlDbType.NVarChar, 50, contact.ContactType),
                Database.MakeInParam("@CurStatus", SqlDbType.NVarChar, 100, contact.ContactStatus),
                Database.MakeInParam("@Fee", SqlDbType.Int, 4, contact.ThisFee),
                Database.MakeInParam("@FeeUsed", SqlDbType.NVarChar, 100, contact.FeeUsed),
                Database.MakeInParam("@ContactContent", SqlDbType.NVarChar, 1000, contact.ContactContent),
                Database.MakeInParam("@NextContactAim", SqlDbType.NVarChar, 1000, contact.NextContactAim),
                Database.MakeInParam("@NextContactTime", SqlDbType.DateTime, 8, contact.NextContactTime),
            };
            data.RunProc("dbo.sproc_CM_UpdateContact", parameters);
            var prams3 = new[]
            {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
            };
            data.RunProc("dbo.sproc_CM_DelContact_Linkman", prams3);
            var prams4 = new[]
            {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
            };
            data.RunProc("dbo.sproc_CM_DelContact_Cooperater", prams4);

            foreach (Cooperater cooperater in contact.arrCooperater)
            {
                var prams1 = new[]
                {
                    Database.MakeInParam("@staffid", SqlDbType.Int, 4, cooperater.StaffID),
                    Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
                };
                data.RunProc("dbo.sproc_CM_AddContact_Cooperater", prams1);
            }
            foreach (Linkman linkman in contact.arrLinkman)
            {
                var prams2 = new[]
                {
                    Database.MakeInParam("@staffid", SqlDbType.Int, 4, linkman.ID),
                    Database.MakeInParam("@contactid", SqlDbType.Int, 4, contact.ID)
                };
                data.RunProc("dbo.sproc_CM_AddContact_Linkman", prams2);
            }
        }

        #endregion

        #region 根据客户ID得到接触信息

        public SqlDataReader GetClientContactInfo(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientContactInfo", parameters);
        }

        #endregion

        #region 根据客户ID得到本周内接触信息

        public SqlDataReader GetClientContactInfo_thisWeek(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientContactInfo_thisWeek", parameters);
        }

        #endregion

        #region 根据接触id得到接触信息

        public Contact GetClientContactStruct(int contactid)
        {
            var contact = new Contact();
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contactid)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetContactByID", parameters))
            {
                while (dr.Read())
                {
                    contact.BargainPrognosis = dr["BargainPrognosis"].ToString();
                    contact.ClientID = Int32.Parse(dr["ClientID"].ToString());
                    contact.ContactAim = dr["ContactAim"].ToString();
                    contact.ContactContent = dr["ContactContent"].ToString();
                    contact.ContactStatus = dr["CurStatus"].ToString();

                    contact.ContactTimes = Int32.Parse(dr["ContactTimes"].ToString());
                    contact.ContactType = dr["ContactType"].ToString();
                    contact.FeeUsed = dr["FeeUsed"].ToString();
                    contact.ID = Int32.Parse(dr["ID"].ToString());
                    contact.NextContactAim = dr["NextContactAim"].ToString();
                    contact.NextContactTime = DateTime.Parse(dr["NextContactTime"].ToString());
                    contact.SellMoney = dr["SellMoney"].ToString();

                    if (dr["contacttime"] != DBNull.Value)
                    {
                        contact.ContactTime = DateTime.Parse(dr["contacttime"].ToString());
                    }
                    else
                    {
                        contact.ContactTime = DateTime.Parse("1900-1-1");
                    }
                }
            }

            SqlParameter[] prams1 = {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contactid)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetContactLinkman", prams1))
            {
                while (dr.Read())
                {
                    var linkman = new Linkman();
                    linkman.Address = dr["Address"].ToString();
                    linkman.ClientID = Int32.Parse(dr["ClientID"].ToString());
                    linkman.Description = dr["Description"].ToString();
                    linkman.Email = dr["Email"].ToString();
                    linkman.Family = dr["Family"].ToString();
                    linkman.Gender = Convert.ToBoolean(dr["Gender"]);
                    linkman.ID = Int32.Parse(dr["ID"].ToString());
                    linkman.Mobile = dr["Mobile"].ToString();
                    linkman.Name = dr["Name"].ToString();
                    linkman.Position = dr["Position"].ToString();
                    linkman.Telephone = dr["Telephone"].ToString();
                    contact.AddLinkman(linkman);
                }
            }

            SqlParameter[] prams2 = {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contactid)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetContactCooperater", prams2))
            {
                while (dr.Read())
                {
                    var cooperater = new Cooperater();
                    cooperater.StaffID = Int32.Parse(dr["CooperatingmanID"].ToString());
                    contact.AddCooperater(cooperater);
                }
            }

            return (contact);
        }

        #endregion

        #region 得到所有协同人

        public SqlDataReader GetAllCooperater()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_GetAllCooperater");
        }

        #endregion

        #region 根据客户id得到协同人

        public SqlDataReader GetCooperaterByClientID(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@ClientID", SqlDbType.Int, 4, clientid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetCooperaterByClientID", parameters);
        }

        #endregion

        #region 根据人员id得到客户列表

        public SqlDataReader GetMyClients(int staffid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@staffid", SqlDbType.Int, 4, staffid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetClientsByStaff", parameters);
        }

        #endregion

        #region 根据人员id得到时间段内的接触情况

        public SqlDataReader GetContactByStaffIDandTime(int staffid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@staffid", SqlDbType.Int, 4, staffid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetContactByStaff", parameters);
        }

        #endregion

        #region 在时间范围内得到销售人员列表

        public SqlDataReader GetSellman(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetSellman", parameters);
        }

        #endregion

        #region 得到时间段接触情况

        public SqlDataReader GetContactInfo(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetContactInfo", parameters);
        }

        #endregion

        #region 得到时间段拜访客户接触情况

        public SqlDataReader GetCallinContactInfo(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetCallinContact", parameters);
        }

        #endregion

        #region 得到时间段内的新客户情况

        public SqlDataReader GetNewClient(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetNewClient", parameters);
        }

        #endregion

        #region 得到时间段内的进入谈判的客户情况

        public SqlDataReader GetNegotiateClient()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_GetNegotiateClient");
        }

        #endregion

        #region 得到时间段内的进入谈判的新客户情况

        public SqlDataReader GetNewNegotiateClient(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetNewNegotiateClient", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间内的新客户情况

        public SqlDataReader GetNewClientBySellman(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetNewClientBySellmanID", parameters);
        }

        #endregion

        #region 得到时间段的3星客户情况

        public SqlDataReader GetNew3StarClient(DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetNew3StarClient", parameters);
        }

        #endregion

        #region 得到全部3星客户情况

        public SqlDataReader Get3StarClient()
        {
            var data = new Database();
            return data.RunDataReader("dbo.sproc_CM_Get3StarClient");
        }

        #endregion

        #region 根据sellmanid得到时间段的3星客户情况

        public SqlDataReader GetNew3StarClientBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetNew3StarClientBySellmanID", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间段的接触过的客户情况

        public SqlDataReader GetContactedClientBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetContactedClientBySellman", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间的拜访过的客户情况

        public SqlDataReader GetCallinClientBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetCallinContactBySellmanID", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间段内的接触情况

        public SqlDataReader GetContactBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetContactBySellmanID", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间段内的拜访接触情况

        public SqlDataReader GeCallinContactBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetCallinContactBySellmanID", parameters);
        }

        #endregion

        #region 得到时间段内销售人员费用情况

        public SqlDataReader GetFeeBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            return data.RunDataReader("dbo.sproc_CM_GetContactFeeBySellmanID", parameters);
        }

        #endregion

        #region 根据sellmanid得到时间段内的差旅费用

        public int GetTravelFeeBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            int result = 0;
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetTravelFeeBySellmanID", parameters))
            {
                while (dr.Read())
                {
                    if (dr["fee"] != DBNull.Value)
                    {
                        result = Int32.Parse(dr["fee"].ToString());
                    }
                }
            }

            return (result);
        }

        #endregion

        #region 根据sellmanid得到时间段内的餐饮费用

        public int GetFoodFeeBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            int result = 0;
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetFoodFeeBySellmanID", parameters))
            {
                while (dr.Read())
                {
                    if (dr["fee"] != DBNull.Value)
                    {
                        result = Int32.Parse(dr["fee"].ToString());
                    }
                }
            }

            return (result);
        }

        #endregion

        #region 根据sellmanid得到时间段内的礼品费用

        public int GetGiftFeeBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            int result = 0;
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetGiftFeeBySellmanID", parameters))
            {
                while (dr.Read())
                {
                    if (dr["fee"] != DBNull.Value)
                    {
                        result = Int32.Parse(dr["fee"].ToString());
                    }
                }
            }

            return (result);
        }

        #endregion

        #region 根据sellmanid得到时间段内的公关费用

        public int GetOuterFeeBySellmanID(int sellmanid, DateTime begintime, DateTime endtime)
        {
            int result = 0;
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@sellmanid", SqlDbType.Int, 4, sellmanid),
                Database.MakeInParam("@begintime", SqlDbType.DateTime, 8, begintime),
                Database.MakeInParam("@endtime", SqlDbType.DateTime, 8, endtime)
            };
            using (SqlDataReader dr = data.RunDataReader("dbo.sproc_CM_GetOuterFeeBySellmanID", parameters))
            {
                while (dr.Read())
                {
                    if (dr["fee"] != DBNull.Value)
                    {
                        result = Int32.Parse(dr["fee"].ToString());
                    }
                }
            }

            return (result);
        }

        #endregion

        #region 上传一个文件得到id号

        public int InsertFile(string path, string type, int pertainid, string extension)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@path", SqlDbType.NVarChar, 500, path),
                Database.MakeInParam("@type", SqlDbType.NVarChar, 50, type),
                Database.MakeInParam("@pertainID", SqlDbType.Int, 4, pertainid),
                Database.MakeInParam("@extension", SqlDbType.NVarChar, 100, extension)
            };

            return (data.RunProc("dbo.sproc_CM_AddFile", parameters));
        }

        #endregion

        #region 根据contactid得到上传附件信息

        public SqlDataReader GetAttachmentByContactID(int contactid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@contactid", SqlDbType.Int, 4, contactid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetAttachmentByContactID", parameters);
        }

        #endregion

        #region 根据clientid得到上传附件信息

        public SqlDataReader GetAttachmentByClientID(int clientid)
        {
            var data = new Database();
            var parameters = new[]
            {
                Database.MakeInParam("@clientid", SqlDbType.Int, 4, clientid)
            };
            return data.RunDataReader("dbo.sproc_CM_GetAttachmentByClientID", parameters);
        }

        #endregion
    }
}